<?php
namespace app\index\controller;
//引入系统数据类
use think\Db;
//引入系统控制器类
use think\Controller;
class Index extends Controller
{
    public function index(){

    }
    /*      补全kaoqin表的数据项
    **      无参数
    **      无返回值
    */
    public function completion(){ 
        $result = Db::name('kaoqin')->select();
        for($i = 1; $i<count($result); $i++) {
            if($result[$i]['id'] == null) {   // 判断id值为空
                $result[$i]['id'] = $result[$i-1]['id'];
                $tmp['num'] = $result[$i]['num'];
                Db::table('kaoqin')->where('num',$result[$i]['num'])->update(['id' => $result[$i]['id']]);
            }
        }
        //    补全空白数据项补全name
        for($i = 1; $i<count($result); $i++) {
            if($result[$i]['name'] == null) {
        //    判断name值为空
                $result[$i]['name'] = $result[$i-1]['name'];
                $tmp['num'] = $result[$i]['num'];
                Db::table('kaoqin')->where('num',$result[$i]['num'])->update(['name' => $result[$i]['name']]);

            }
            print_r($result[$i]['name']);
        }

    }
    /*      筛选单条数据，补全kaoqin_example表的数据项
    **      无参数
    **      无返回值
    */
    public function changeDb(){
        for($j=1;$j<=31;$j++){
            // var_dump($j."day");
            $result = Db::name('kaoqin')
            ->where($j."day",'>','0')
            ->select();
            // var_dump($result);
            $count=1;
            for($i=1;$i<count($result);$i++){
                if($result[$i]['id']==$result[$i-1]['id']){
                    $count++;
                    if($i==count($result)-1){
                        var_dump($i,$count);
                        $this->process($count,$result,$i+1,$j);
                        // $count=1; 
                    }
                }else{
                    $this->process($count,$result,$i,$j);
                    $count=1; 
                    }
                }
        }

    }
    /*      筛选单条数据，回滚数据,此函数作用：提取相同代码段，简化代码
    **      $count,$result,$i,$j  循环变量
    **      调用回滚写入函数text()
    */
    public function process($count,$result,$i,$j){
        if($count%2!=1){
            for($a=$count;$a>0;$a--){  
                $this->text($result[$i-$a],$j);
            }
        }else{
            for($a=$count;$a>1;$a--){
                $this->text($result[$i-$a],$j);
            }
        }
    }
    /*      回滚时间戳函数
    **      $result 有价值非单条数据    $j 具体那一天
    **      无返回值，写入kaoqin_example表
    */
    public function text($result,$j){
        if($j<10){
            $date = date('Ym').'0'.$j;
        }
        else{
            $date = date('Ym').$j;
        }
        $time = strtotime($date.$result[$j."day"]);
        $endInsertTime = strtotime(date('Ymd').'12:00:00');
        $data = ['id'=>$result['id'],'name'=>$result['name'],'time'=>$time,'endtime'=>$endInsertTime];     
        //插入数据库
        Db::table('kaoqin_example')->insert($data);
        
    }
    /*      签到总时长
    **      参数 $startTime,$endTime 起始时间 结尾时间 默认单位是时间戳
    **      返回值：学生姓名和对应的时间
    */
    public function Otime($startTime=null,$endTime=null){
        $maxEndTime = Db::table('kaoqin_example')->max('endtime');
        $startTime = $startTime==null?strtotime(date('Ym').'01 00:00:00'):$startTime;
        $endTime = $endTime==null?$maxEndTime:$endTime;
        $result = Db::table('kaoqin_example')
        ->where('time','between',[$startTime,$endTime])
        ->select();
        $arr = [];
        $alltime=[];
        if($result){
            foreach($result as $key=>$value){
                $arr[$value['name']][] = $value;
            }
            foreach($arr as $key=>$value){
                for($i=1;$i<count($arr[$key]);$i+=2){
                    array_key_exists($key,$alltime)?$alltime[$key]+=$arr[$key][$i]['time']-$arr[$key][$i-1]['time']:$alltime[$key]=$arr[$key][$i]['time']-$arr[$key][$i-1]['time'];
                }
            }
            foreach($alltime as $key=>$value) {
                $alltime[$key] = ceil($alltime[$key]/3600); 
            }
            return $alltime;
        } else return [];
        
    }
    /*      签到频率
    **      参数 $startTime,$endTime 起始时间 结尾时间 默认单位是时间戳
    **      返回值：学生姓名和对应的频率    
    */
    public function Opin($startTime=null,$endTime=null){
        $maxEndTime = Db::table('kaoqin_example')->max('endtime');
        $startTime = $startTime==null?strtotime(date('Ym').'01 00:00:00'):$startTime;
        $endTime = $endTime==null?$maxEndTime:$endTime;
        $result = Db::table('kaoqin_example')
        ->where('time','between',[$startTime,$endTime])
        ->select();
        $arr = [];
        $alltime=[];
        if($result){
            foreach($result as $key=>$value){
                $arr[$value['name']][] = $value;
            }
            foreach($arr as $key=>$value){
                $arr[$key]=count($arr[$key])/2;
            }
            return $arr;
        }
        else return [];
        
    }
    /*      更新最新月份的studentform表格
    **      无参数 期间调用Otime Opin 函数进行数据获取
    **      无返回值
    */
    public function updateUser($startTime=null,$endTime=null){
            $startTime = strtotime('20181001 00:00:00');
            $endTime = Db::table('kaoqin_example')->max('endtime');
            $time = $this->Otime($startTime,$endTime);                                   
            $frequency = $this->Opin($startTime,$endTime);
            $result = Db::table('studentform')->select();  //查到所有人
            foreach($time as $key=>$value){       //遍历更新表
                for($j=0;$j<count($time[$key]);$j++){
                    Db::table('studentform')->where('name',$key )->update(['time' => $time[$key]]);
                }
            }
            foreach($frequency as $key=>$value){
                for($j=0;$j<count($frequency[$key]);$j++){
                    Db::table('studentform')->where('name',$key)->update(['frequency' => $frequency[$key]]);
                }
            }
        
    }
    /*      获取的已知信息补全,查询本月的所有人的信息
    **      无参数 期间调用Otime Opin 函数进行数据获取
    **      返回值：$arrResult 返回所有人的信息  返回numid作为主键 studentid学号 name 姓名 time总时长 frequence 频率 默认时间是一个月
    */
    public function getAllUser($startTime=null,$endTime=null){
        if($startTime && $endTime){
            $startTime = strtotime($startTime.' 00:00:00');
            $endTime = strtotime($endTime.' 12:00:00');
        }
        $arrResult = Db::table('studentform')->order('time desc')->select();
        return $arrResult;
    }
    /*      按学号或者姓名获得所有信息
    **      参数：$name 姓名或者学号
    **      返回值 $result[0] 返回numid作为主键 studentid学号 name 姓名 time总时长 frequence 频率 默认时间是一个月
    */
    public function searchUser($name){
        $result = Db::table('studentform')  //查到所有人
        ->where('name','like',"%".$name."%")
        ->whereOr('studentid','like',"%".$name."%")
        ->order('time desc')
        ->select();//查到具体人
        return $result;  //返回$result[0]原因，只有一条查询记录
        
    }
    /*      在kaoqin_example补全学号
    **      无参数
    **      无返回值
    */
    public function searchTime($startTime=null,$endTime=null){
        if($startTime && $endTime){
            $startTime = strtotime($startTime.' 00:00:00');
            $endTime = strtotime($endTime.' 12:00:00');
        }
        else{
            $startTime = strtotime('20181001 00:00:00');
            $endTime = Db::table('kaoqin_example')->max('endtime');
        }
        $time = $this->Otime($startTime,$endTime);                                   
        $frequency = $this->Opin($startTime,$endTime);
        // var_dump($time,$frequency);
        $result = Db::table('studentform')->select();  //查到所有人
        for($j=0;$j<count($result);$j++){           
            $result[$j]['time'] = 0;
            $result[$j]['frequency'] = 0;
            foreach($time as $key=>$value){
                if($result[$j]['name'] == $key){
                    $result[$j]['time'] = $time[$key];
                    // var_dump($time[$key],$result[$j]['time']);
                }
            }
            foreach($frequency as $key=>$value){
                if($result[$j]['name'] == $key){
                    $result[$j]['frequency'] = $frequency[$key];
                }
            }
        }
        foreach ($result as $key => $value) {
            $r[] = $value['time'];
        }
        array_multisort($r,SORT_DESC, $result,SORT_DESC);
        // var_dump($result);
        return $result;  
    }
    public function sort(){
        $arrResult = Db::table('studentform')->order('studentid asc')->select();
        return $arrResult;
    }
    public function compareTime($startTime,$endTime){
        $time = $this->Otime($startTime,$endTime);                                   
        $frequency = $this->Opin($startTime,$endTime);
        // var_dump($time,$frequency);
        $result = Db::table('studentform')->select();  //查到所有人
        for($j=0;$j<count($result);$j++){           
            $result[$j]['time'] = 0;
            $result[$j]['frequency'] = 0;
            foreach($time as $key=>$value){
                if($result[$j]['name'] == $key){
                    $result[$j]['time'] = $time[$key];
                    // var_dump($time[$key],$result[$j]['time']);
                }
            }
            foreach($frequency as $key=>$value){
                if($result[$j]['name'] == $key){
                    $result[$j]['frequency'] = $frequency[$key];
                }
            }
        }
        foreach ($result as $key => $value) {
            $r[] = $value['studentid'];
        }
        array_multisort($r,SORT_ASC, $result,SORT_ASC);
        return $result; 
    }
    public function compare(){
        $date = date('Y');
        $month = date('m') -1 ;
        if($month == 0){
            $date = date('Y')-1;
            $month = 12;
        }
        $res1 = $this->searchTime($date.$month.'01',$date.$month.'31');
        foreach ($res1 as $key => $value) {
            $r[] = $value['studentid'];
        }
        array_multisort($r,SORT_ASC, $res1,SORT_ASC);
        $res2 = $this->searchTime(date('Ym').'01',date('Ym').'31');
        foreach ($res2 as $key => $value) {
            $rr[] = $value['studentid'];
        }
        array_multisort($rr,SORT_ASC, $res2,SORT_ASC);
        // var_dump(date('Ym').'31');
        return $arrResult = [$res1,$res2];
        // return $res2;
    }
    public function temp($startTime,$endTime){ //测试生成sql用的
        $subQuery = Db::table('kaoqin_example')
            ->where('time','between',[$startTime,$endTime])
            ->fetchSql(true)
            ->select();
        // var_dump($subQuery);
        var_dump(strtotime('20190104 18:00:00'));
    }
    


}
